import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

public class ExcelUtil {

    /**
     * 读取excel
     * @param filePath
     * @return
     * @throws Exception
     */
    public List<List<String>> readExcel(String filePath) throws Exception {
        List<List<String>> rowList = null;
        File file = new File(filePath);
        if (!file.exists()) {
            throw new RuntimeException("文件不存在");
        }
        String fileName = file.getName();
        FileUtil.FileType fileType = FileUtil.getFileType(fileName);
        switch (fileType) {
            case XLS:
                rowList = readExcelXls(file);
                break;
            case XLSX:
                rowList = readExcelXlsx(file);
                break;
            case UNKOWN:
                throw new RuntimeException("文件类型不正确");
        }
        return rowList;
    }

    /**
     * 读取xls文件
     * xls 是一个特有的二进制格式，其核心结构是复合文档类型的结构
     * xls是Excel 2003及以前版本生成的文件格式
     * @param file
     * @return
     * @throws Exception
     */
    private List<List<String>> readExcelXls(File file) throws Exception {
        List<List<String>> rowList;
        FileInputStream fileIn = null;
        try {
            fileIn = new FileInputStream(file);
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowList = getRowList(rowIterator);
        }
        catch (IOException e) {
            throw e;
        }
        finally {
            if (fileIn != null) {
                try {
                    fileIn.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rowList;
    }

    /**
     * 读取xlsx文件
     * xlsx的核心结构是 XML类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小
     * xlsx是Excel 2007及以后版本生成的文件格式。
     * @param file
     * @return
     * @throws Exception
     */
    private List<List<String>> readExcelXlsx(File file) throws Exception {
        List<List<String>> rowList;
        FileInputStream fileIn = null;
        try {
            fileIn = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fileIn);
            XSSFSheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowList = getRowList(rowIterator);
        }
        catch (IOException e) {
            throw e;
        }
        finally {
            if (fileIn != null) {
                try {
                    fileIn.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rowList;
    }

    /**
     * 获取excel行列数据
     * @param rowIterator
     * @return
     */
    private List<List<String>> getRowList(Iterator<Row> rowIterator) {
        List<List<String>> rowList = new ArrayList<>();
        while (rowIterator.hasNext()) {
            Row row = rowIterator.next();
            List<String> cellList = getCellList(row);
            rowList.add(cellList);
        }
        return rowList;
    }

    /**
     * 读取excel列数据
     * @param row
     * @return
     */
    private List<String> getCellList(Row row) {
        List<String> cellList = new ArrayList<>();
        Iterator<Cell> cellIterator = row.cellIterator();
        while (cellIterator.hasNext()) {
            Cell cell = cellIterator.next();
            //判断单元格格式
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    cellList.add(String.valueOf(cell.getNumericCellValue()));
                    break;
                case STRING:
                    cellList.add(cell.getStringCellValue());
                    break;
                case _NONE:
                case BLANK:
                    cellList.add("");
                    break;
            }
        }
        return cellList;
    }

    /**
     * 读取excel
     * @param filePath
     * @return
     * @throws Exception
     */
    public List<RowBean> readExcelToListBean(String filePath) throws Exception {
        List<RowBean> rowList = null;
        File file = new File(filePath);
        if (!file.exists()) {
            throw new RuntimeException("文件不存在");
        }
        String fileName = file.getName();
        FileUtil.FileType fileType = FileUtil.getFileType(fileName);
        switch (fileType) {
            case XLS:
                rowList = readExcelXlsToListBean(file);
                break;
            case XLSX:
                rowList = readExcelXlsxToListBean(file);
                break;
            case UNKOWN:
                throw new RuntimeException("文件类型不正确");
        }
        return rowList;
    }

    /**
     * 读取xls文件
     * xls 是一个特有的二进制格式，其核心结构是复合文档类型的结构
     * xls是Excel 2003及以前版本生成的文件格式
     * @param file
     * @return
     * @throws Exception
     */
    private List<RowBean> readExcelXlsToListBean(File file) throws Exception {
        List<RowBean> rowList;
        FileInputStream fileIn = null;
        try {
            fileIn = new FileInputStream(file);
            POIFSFileSystem fs = new POIFSFileSystem(fileIn);
            HSSFWorkbook wb = new HSSFWorkbook(fs);
            HSSFSheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowList = getRowListBean(rowIterator);
        }
        catch (IOException e) {
            throw e;
        }
        finally {
            if (fileIn != null) {
                try {
                    fileIn.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rowList;
    }

    /**
     * 读取xlsx文件
     * xlsx的核心结构是 XML类型的结构，采用的是基于 XML 的压缩方式，使其占用的空间更小
     * xlsx是Excel 2007及以后版本生成的文件格式。
     * @param file
     * @return
     * @throws Exception
     */
    private List<RowBean> readExcelXlsxToListBean(File file) throws Exception {
        List<RowBean> rowList;
        FileInputStream fileIn = null;
        try {
            fileIn = new FileInputStream(file);
            XSSFWorkbook wb = new XSSFWorkbook(fileIn);
            XSSFSheet sheet = wb.getSheetAt(0);
            Iterator<Row> rowIterator = sheet.rowIterator();
            rowList = getRowListBean(rowIterator);
        }
        catch (IOException e) {
            throw e;
        }
        finally {
            if (fileIn != null) {
                try {
                    fileIn.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
        return rowList;
    }

    /**
     * 读取行数据
     * @param rowIterator
     * @return
     */
    private List<RowBean> getRowListBean(Iterator<Row> rowIterator) {
        List<String> headList = null;
        List<RowBean> rowList = new ArrayList<>();
        while (rowIterator.hasNext()) {
            RowBean rowBean = new RowBean();
            Row row = rowIterator.next();
            if (row.getRowNum() == 0) {
                headList = getCellList(row);
                continue;
            }
            List<RowBean.CellBean> cellList = rowBean.getCellBeanList();
            cellList.addAll(getCellListBean(headList, row));
            rowList.add(rowBean);
        }
        return rowList;
    }

    /**
     * 读取列数据
     * @param headList
     * @param row
     * @return
     */
    private List<RowBean.CellBean> getCellListBean(List<String> headList, Row row) {
        List<RowBean.CellBean> cellList = new ArrayList<>();
        Iterator<Cell> cellIterator = row.cellIterator();
        int i = 0;
        while (cellIterator.hasNext()) {
            RowBean.CellBean cellBean = new RowBean.CellBean();
            Cell cell = cellIterator.next();
            String cellName = headList.get(i);
            if (cellName != null) {
                cellBean.setCellName(cellName);
            }
            //判断单元格格式
            switch (cell.getCellTypeEnum()) {
                case NUMERIC:
                    cellBean.setCellValue(String.valueOf(cell.getNumericCellValue()));
                    break;
                case STRING:
                    cellBean.setCellValue(cell.getStringCellValue());
                    break;
                case _NONE:
                case BLANK:
                    cellBean.setCellValue("");
                    break;
            }
            cellList.add(cellBean);
            i++;
        }
        return cellList;
    }
}
